package com.libc.yygh.common;

import com.libc.yygh.common.bean.BeanHelper;
import com.libc.yygh.common.constant.IPubCons;
import com.libc.yygh.common.constant.ISqlCons;

import java.text.MessageFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/***
 * 处理和SQL语句相关的工具类
 *
 * @version 1.0<br>
 * @qualiFild nc.pub.itf.tools.sql.SQLHelper.java<br>
 * @author：licheng<br>
 * @date Created on 2019-8-12<br>
 */
public class SqlHelper implements IPubCons, ISqlCons
{
    static Integer DEFAULT_SIZE = 1000;
    static String tempTable = "sys_tempTable_";

    public static String queryTableByOrcl(String tableName) throws Exception
    {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * FROM (");
        sql.append(" SELECT");
        sql.append(" dt.table_name  AS \"code\",");
        sql.append(" replace(initcap(lower(substr(dt.table_name, instr(dt.table_name, '_')))), '_', '')");
        sql.append(" AS \"classname\",");
        sql.append(" dt.table_name  AS \"tablename\",");
        sql.append(" dtc.comments   AS \"tablecomments\",");
        sql.append(" MAX(uo.last_ddl_time) AS \"createtime\",");
        sql.append(" uo.object_type  AS \"tabletype\",");
        sql.append(" ROWNUM   AS rn");
        sql.append(" FROM");
        sql.append(" user_tables dt");
        sql.append(" LEFT JOIN user_tab_comments   dtc ON dtc.table_name = dt.table_name");
        sql.append(" LEFT JOIN user_objects        uo ON uo.object_name = dt.table_name");
        sql.append(" WHERE");
        sql.append(" uo.object_type IN (  'TABLE', 'VIEW' )");
        sql.append(" AND 1 = 1");
        if (!PubEnvUtil.isEmpty(tableName))
        {
            sql.append(" AND ( dt.table_name LIKE '%").append(tableName.toLowerCase()).append("%'");
            sql.append("  OR dtc.table_name LIKE '%").append(tableName.toUpperCase()).append("%' )");
        }
        sql.append(" GROUP BY");
        sql.append(" ROWNUM,");
        sql.append(" dt.table_name,");
        sql.append(" dtc.comments,");
        sql.append(" uo.object_type");
        sql.append(" ORDER BY");
        sql.append(" dt.table_name,dtc.comments ");
        sql.append(" )");

        return String.valueOf(sql);
    }


    /************************************************************** <br>
     *
     *说明: 根据表名得到查询列名的SQL语句<br>
     * @Description   <br>
     * @Date Created on 2021/12/24  17:50  <br>
     * @param tableName <br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getColumnByTableOrcl(String tableName)
    {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT");
        sql.append(" user_tab_columns.table_name         AS \"table_code\",/**表名*/");
        sql.append(" user_tab_columns.table_name         AS \"tablename\",/**表名*/");
        sql.append(" lower(user_tab_columns.table_name)  AS \"table_cod_low\",/**表名*/");
        sql.append(" lower(user_tab_columns.table_name)  AS \"tablenamelow\",/**表名*/");
        sql.append(" user_tab_comments.comments          AS \"tablecomments\",/**表备注*/");
        sql.append(" user_tab_comments.table_type        AS \"table_type\",/**表备注*/");
        sql.append(" user_tab_columns.column_name        AS \"col_code\",/**列名*/");
        sql.append(" user_tab_columns.column_name        AS \"columnname\",/**列名*/");
        sql.append(" user_col_comments.comments          AS \"col_name\",/**列备注*/");
        sql.append(" user_col_comments.comments          AS \"unicode_comments\",/**列备注*/");
        sql.append(" user_col_comments.comments          AS \"columncomments\",/**列备注*/");
        sql.append(" user_tab_columns.data_type          AS \"col_type\",/**类型*/");
        sql.append(" user_tab_columns.data_type          AS \"data_type\" ,/**类型*/");
        sql.append(" user_tab_columns.data_type          AS \"datatype\" ,/**类型*/");
        sql.append(" decode(user_tab_columns.nullable, 'N', 'true', 'false') AS \"nullable\",/**是否可空*/");
        sql.append(" user_tab_columns.data_length        AS \"data_length\",/**数据长度*/");
        sql.append(" user_tab_columns.data_length        AS \"datalength\",/**数据长度*/");
        sql.append(" user_tab_columns.data_precision     AS \"data_precision\",/**数据精度*/");
        sql.append(" user_tab_columns.data_precision     AS \"dataprecision\",/**数据精度*/");
        sql.append(" user_tab_columns.data_scale         AS \"data_scale\",/**数据小数位*/ ");
        sql.append(" user_tab_columns.data_scale         AS \"datascale\",/**数据小数位*/ ");
        sql.append(" decode(pk.column_name, NULL, 'false', 'true') AS \"pk\"/**是否主键*/");
        sql.append(" FROM user_tab_cols/**user_tab_columns*/    user_tab_columns");
        sql.append(" LEFT JOIN user_tab_comments   user_tab_comments ");
        sql.append(" ON user_tab_columns.table_name = user_tab_comments.table_name");
        sql.append(" JOIN user_col_comments   user_col_comments");
        sql.append(" ON user_col_comments.table_name = user_tab_columns .table_name");
        sql.append(" AND user_col_comments.column_name = user_tab_columns.column_name");
        sql.append(" LEFT JOIN (");
        sql.append(" SELECT");
        sql.append(" user_cons_columns.table_name,");
        sql.append(" user_cons_columns.column_name");
        sql.append(" FROM");
        sql.append(" user_cons_columns user_cons_columns,");
        sql.append(" user_constraints user_constraints");
        sql.append(" WHERE");
        sql.append(" user_cons_columns.constraint_name = user_constraints.constraint_name");
        sql.append(" AND user_constraints.constraint_type = 'P'");
        sql.append(" AND (user_constraints.table_name = '").append(tableName.toUpperCase());
        sql.append("'OR user_constraints.table_name = '").append(tableName.toLowerCase());
        sql.append("')) pk ON pk.column_name = user_tab_columns.column_name");
        sql.append(" WHERE");
        sql.append(" (user_tab_columns.table_name = '").append(tableName.toUpperCase());
        sql.append("'OR user_tab_columns.table_name = '").append(tableName.toLowerCase());
        sql.append("') ORDER BY user_tab_columns.column_id ASC");

        return sql.toString();
    }

    public static String queryColumnByMysql(String tableName, String group) throws Exception
    {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT");
        sql.append(" table_name       AS table_code,/**表名*/");
        sql.append(" column_name      AS col_code,/**字段名称*/");
        sql.append(" column_comment   AS col_name,/** 字段说明*/");
        sql.append(" column_type      AS col_type,/**字段类型长度*/");
        sql.append(" is_nullable      AS nullable,/**是否可空*/");
        sql.append(" if(extra = \"auto_increment\", concat(column_key, \"(\", ");
        sql.append("   if(extra = \"auto_increment\", \"自增\", extra), \")\"),");
        sql.append("     column_key)) as pk/**是否主键*/");
        sql.append(" FROM information_schema.columns");
        sql.append(" /**数据库名：jn_power 表名 rpt_cap_hour_ammeter_201810*/");
        sql.append(" WHERE  table_schema = ? AND table_name = ? ;");
        sql.append(" ");

        return sql.toString();
    }


    /************************************************************** <br>
     *
     *说明: <br>
     *
     * @Description   <br>
     * @Date Created on 2021/10/21  17:19  <br>
     * @Param [pks] <br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getPkInSQL4InSizeGEQHus(Collection< Object > colObj, Object key, String wherecolumn)
    {
        Collection pks = colObj.stream().
                               map(obj ->
                                   {
                                       if (obj instanceof Map)
                                       { return StringUtil.valueOfZero(((Map) obj).get(StringUtil.valueOfZero(key))); }
                                       else
                                       { return StringUtil.valueOfZero(obj); }
                                   }).collect(Collectors.toSet());

        AtomicReference< String > subInSql = new AtomicReference<>(EMPTY);
        if (!Optional.ofNullable(pks).isPresent()) { return subInSql.get(); }

        Object[] objs = pks.stream().toArray();
        int valueSize = PubEnvUtil.getSize(objs);
        //每次拼接800个in字段
        int num = 800;
        // 值的个数
        // 批次数
        StringBuffer sql = new StringBuffer();
        int batchSize = valueSize / num + (valueSize % num == 0 ? 0 : 1);
        for (int i = 0; i < batchSize; i++)
        {
            if (i > 0)
            { sql.append(RIGHT_BRACKET).append(or); }
            sql.append(wherecolumn).append("in").append(LEFT_BRACKET);
            for (int j = i * num; (j < (i + 1) * num) && j < valueSize; j++)
            {
                if (j > i * num)
                { sql.append(COMMA); }
                sql.append(SINGLE_QUOTE).append(objs[j]).append(SINGLE_QUOTE);
            }
        }
        sql.append(RIGHT_BRACKET);
        subInSql.set(sql.toString());

        return subInSql.get();

    }

    public static String getPkInSQLByCol(String col, Collection pks)
    { return col + " IN(" + getPkInSQL(pks) + ")"; }

    public static String getPkInSQL(Collection pks)
    {
        AtomicReference< String > subInSql = new AtomicReference<>("");
        if (!Optional.ofNullable(pks).isPresent()) { return subInSql.get(); }

        Object[] keys = pks.stream().toArray();
        if (PubEnvUtil.getSize(keys) <= DEFAULT_SIZE.intValue())
        {
            for (int i = 0; i < PubEnvUtil.getSize(keys); i++)
            {
                Object obj = keys[i];
                if (i == 0) { subInSql.updateAndGet(str -> (" '" + obj + "' ")); }
                else { subInSql.updateAndGet(str -> str + (", '" + obj + "' ")); }
            }
        }
        // else
        // {
        //     String tableName = tempTable + System.currentTimeMillis();
        //     HrBaseDao.update(" CREATE TABLE " + tableName + "(pk_temptable varchar(36), ts char(19))");
        //     StringBuffer insertSQL = new StringBuffer();
        //     insertSQL.append(" INSERT ALL ");
        //     // insertSQL.append(" INSERT INTO ").append(tableName).append("(pk_temptable,ts) VALUES");
        //     pks.stream()
        //        .forEach(key ->
        //                 {
        //                     // String insertSQL = " INSERT INTO " + tableName + " (pk_temptable,ts) VALUES(?,?)";
        //                     // HrBaseDao.update(insertSQL, key, DateUtilbc.currentDateTime());
        //                     insertSQL.append(" INTO ").append(tableName).append("(pk_temptable,ts) VALUES('")
        //                              .append(key)
        //                              .append("','")
        //                              .append(DateUtilbc.currentDateTime())
        //                              .append("')");
        //                 });
        //     // insertSQL.deleteCharAt(insertSQL.length() - 1);
        //     insertSQL.append("select 1 FROM dual ");
        //     HrBaseDao.update(StringUtil.valueOfEmpty(insertSQL));
        //     String sql = StringUtil.valueOf(getSelectSQL(tableName, new String[]{"pk_temptable"}));
        //     LoggerUtil.debugFormat(Boolean.TRUE, "IN條件大於900，使用临时表SQL：%s,key一共[%s]条", sql,
        //                                              PubEnvUtil.getSize(pks));
        //     subInSql.set(sql);
        // }

        return subInSql.get();
    }

    /************************************************************** <br>
     *
     *说明: <br>
     *
     * @Description   <br>
     * @Date Created on 2021/10/21  17:19  <br>
     * @Param [pks] <br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getPkInSQL(Collection colObj, Object key)
    {

        // return getPkInSQL(colObj.stream().
        //                         map(obj ->
        //                             {
        //                                 if (obj instanceof Map)
        //                                 { return StringUtil.valueOfZero(((Map) obj).get(StringUtil.valueOfZero
        //                                 (key))); }
        //                                 else if (BeanHelper.isBeanClass(obj.getClass()))
        //                                 { return StringUtil.valueOfZero((CollectionUtil. transBean2Map(obj)).get
        //                                 (StringUtil.valueOfZero(key))); }
        //                                 else
        //                                 { return StringUtil.valueOfZero(obj); }
        //                             }).collect(Collectors.toSet()));

        return getPkInSQL(new HashSet< String >()
        {{
            colObj.stream().forEach(obj ->
                                    {
                                        if (obj instanceof Map)
                                        { add(StringUtil.valueOfZero(((Map) obj).get(StringUtil.valueOfZero(key)))); }
                                        else if (BeanHelper.isBeanClass(obj.getClass()))
                                        {
                                            try
                                            {
                                                add(StringUtil.valueOfZero((CollectionUtil.transBean2Map(obj)).get(StringUtil.valueOfZero(key))));
                                            }
                                            catch (Exception e)
                                            {
                                                throw new RuntimeException(e);
                                            }
                                        }
                                        else { add(StringUtil.valueOfZero(obj)); }
                                    });
        }});
    }

    private int iCount = 200;
    @SuppressWarnings ("rawtypes")
    public static final HashMap< Class,String > hashFieldSQL = new HashMap< Class,String >()
    {
        private static final long serialVersionUID = -2518812495109758605L;

        {
            put(String.class, " varchar(200) null");

            put(int.class, " smallint null");
            put(Integer.class, " smallint null");

            put(boolean.class, " char(1) null");
            put(Boolean.class, " char(1) null");

            put(Date.class, " char(10) null");

            put(double.class, " decimal(16,4) null");
            put(Double.class, " decimal(16,4) null");
        }
    };

    public int getiCount( )
    { return iCount; }

    public void setiCount(int iCount)
    { this.iCount = iCount; }

    /***
     * *********************************************************** <br>
     * 说明：拼接两个SQL语句中的条件表达式
     *
     * @String
     * @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:07:58
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    public static String appendExtraCond(String cond, String extraCond)
    {
        if (PubEnvUtil.isEmpty(cond) && PubEnvUtil.isEmpty(extraCond))
        { return null; }

        if (PubEnvUtil.isEmpty(extraCond))
        { return cond; }

        if (PubEnvUtil.isEmpty(cond))
        { return extraCond; }

        if (extraCond.trim().startsWith(LEFT_BRACKET) && extraCond.trim().endsWith(RIGHT_BRACKET))
        { return cond + and + extraCond; }

        return cond + and + LEFT_BRACKET + extraCond + RIGHT_BRACKET;
    }

    /****************************************************************************
     * 逻辑型字段的空值判断SQL片断<br>
     * Created on 2011-3-4 15:56:25<br>
     * @param strFieldCode
     * @return isnull(field, ' N ')='N'
     * @author zc
     ***************************************************************************/
    public static String getBoolNullSql(String strFieldCode)
    { return MessageFormat.format(" isnull({0},''{1}'')=''{1}'' ", strFieldCode, '~'); }

    /****************************************************************************
     * <br>
     * Created on 2011-3-4 15:02:05<br>
     * @param strFieldCode
     * @return strFieldCode='~'
     * @author zc
     ***************************************************************************/
    public static String getEqualsWaveSql(String strFieldCode)
    {
        return " " + strFieldCode + "='~' ";
    }

    /****************************************************************************
     * 文本型字段的空值判断SQL片断<br>
     * Created on 2011-3-4 15:56:25<br>
     * @param strFieldCode
     * @return isnull(strFieldCode, ' ~ ')='~'
     * @author zc
     ***************************************************************************/
    public static String getNullSql(String strFieldCode)
    { return MessageFormat.format(" isnull({0},''{1}'')=''{1}'' ", strFieldCode, '~'); }

    /***
     * *********************************************************** <br>
     * 说明：数值型字段的空值判断SQL片断
     *
     * @String
     * @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:40:12
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    public static String getNumberNullSql(String strFieldCode)
    { return MessageFormat.format(" isnull(cast({0} as char),''{1}'')=''{1}'' ", strFieldCode, '~'); }

    /***
     * *********************************************************** <br>
     * 说明：
     *
     * @param strValues
     * @return 把strValues数组中的合并成一个或者多个可以用于in语句的字符串
     * @String[] @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:43:08
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    protected static String[] joinToInSql(String... strValues)
    {
        if (strValues == null || strValues.length == 0)
        { return null; }
        // 每组只有100个值用来拼in的语句
        int iCount = 100;
        List< String > arrListInSQL = new ArrayList< String >();
        // 已经存在的值，以保证不重复
        List< String > arrListExistValue = new ArrayList< String >();
        StringBuilder strbdrInSQL = new StringBuilder();
        for (int i = 0, iIndex = 0; i < strValues.length; i++)
        {
            if (PubEnvUtil.isBlank(strValues[i]) || arrListExistValue.contains(strValues[i].trim()))
            { continue; }
            iIndex++;
            arrListExistValue.add(strValues[i].trim());
            strbdrInSQL.append(",'").append(strValues[i]).append(SINGLE_QUOTE);
            if (iIndex == iCount || i == strValues.length - 1)
            {
                if (strbdrInSQL.toString().trim().length() > 0)
                { arrListInSQL.add(strbdrInSQL.substring(1)); }
                iCount = 0;
                strbdrInSQL = new StringBuilder();
            }
        }

        if (strbdrInSQL.length() > 0 && !arrListInSQL.contains(strbdrInSQL.toString()))// 保证最后一个也在in语句的列表中
        { arrListInSQL.add(strbdrInSQL.substring(1)); }

        return arrListInSQL.toArray(new String[0]);
    }

    /***
     * *********************************************************** <br>
     * 说明：
     *
     * @param strValues
     * @param iCount    每组只有 iCount 个值用来拼in的语句，如果为 -1，则不限长度
     * @return 把strValues数组中的合并成一个或者多个可以用于in语句的字符串，返回值形式：
     * @String
     * @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:43:55
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    public static String joinToInSql(String strValues[], int iCount)
    {
        if (strValues == null || strValues.length == 0)
        { return null; }

        List< String > arrListInSQL = new ArrayList< String >();
        // 已经存在的值，以保证不重复
        List< String > arrListExistValue = new ArrayList< String >();
        StringBuilder strbdrInSQL = new StringBuilder();
        for (int i = 0, iIndex = 0; i < strValues.length; i++)
        {
            if (PubEnvUtil.isBlank(strValues[i]) || arrListExistValue.contains(strValues[i].trim()))
            { continue; }
            iIndex++;
            arrListExistValue.add(strValues[i].trim());
            strbdrInSQL.append(COMMA).append(SINGLE_QUOTE).append(transfer(strValues[i])).append(SINGLE_QUOTE);
            if (iCount == -1 && i == strValues.length - 1)
            {
                arrListInSQL.add(strbdrInSQL.substring(1));
                continue;
            }
            if (iIndex == iCount || i == strValues.length - 1)
            {
                if (strbdrInSQL.toString().trim().length() > 0)
                { arrListInSQL.add(strbdrInSQL.substring(1)); }

                iCount = 0;
                strbdrInSQL = new StringBuilder();
            }
        }

        if (strbdrInSQL.length() > 0 && !arrListInSQL.contains(strbdrInSQL.toString()))// 保证最后一个也在in语句的列表中
        { arrListInSQL.add(strbdrInSQL.substring(1)); }

        return arrListInSQL.isEmpty() ? null : arrListInSQL.get(0);
    }

    /***
     * s *********************************************************** <br>
     * 说明：
     *
     * @String[] @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:06:23
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    protected static String[] joinToInSqls(String strValues[], int iCount)
    {
        if (strValues == null || strValues.length == 0)
        {
            return null;
        }

        List< String > arrListInSQL = new ArrayList< String >();
        List< String > arrListExistValue = new ArrayList< String >();// 已经存在的值，以保证不重复

        StringBuilder strbdrInSQL = new StringBuilder();

        for (int i = 0, iIndex = 0; i < strValues.length; i++)
        {

            if ((PubEnvUtil.isBlank(strValues[i])) || arrListExistValue.contains(strValues[i].trim()))
            {
                continue;
            }

            iIndex++;
            arrListExistValue.add(strValues[i].trim());

            strbdrInSQL.append(",'").append(strValues[i]).append(SINGLE_QUOTE);

            if (iCount == -1 && i == strValues.length - 1)
            {
                arrListInSQL.add(strbdrInSQL.substring(1));
                continue;
            }

            if (iIndex == iCount || i == strValues.length - 1)
            {
                if (strbdrInSQL.toString().trim().length() > 0)
                {
                    arrListInSQL.add(strbdrInSQL.substring(1));
                }

                iIndex = 0;
                strbdrInSQL = new StringBuilder();
            }
        }

        if (strbdrInSQL.length() > 0 && !arrListInSQL.contains(strbdrInSQL.toString()))// 保证最后一个也在in语句的列表中
        {
            arrListInSQL.add(strbdrInSQL.substring(1));
        }

        return arrListInSQL.toArray(new String[0]);
    }

    /***************************************************************
     * <br>
     * Created on 2012-5-30 19:36:48<br>
     * @param strFieldCode
     * @param iCount
     * @param strValues 每个里面根据需要加上“%”
     * @return String[] (strFieldCode like 'strValue[0]' or ... or strFieldCode like 'strValue[n]'
     * @author Rocex Wang
     **************************************************************/
    public static String[] joinToLikeSqls(String strFieldCode, int iCount, String... strValues)
    {
        if (strValues == null || strValues.length == 0)
        {
            return null;
        }

        List< String > listLikeSQL = new ArrayList< String >();
        List< String > listExistValue = new ArrayList< String >();// 已经存在的值，以保证不重复

        StringBuilder strbdrInSQL = new StringBuilder();

        for (int i = 0, iIndex = 0; i < strValues.length; i++)
        {
            if (PubEnvUtil.isBlank(strValues[i]) || listExistValue.contains(strValues[i].trim()))
            {
                continue;
            }
            iIndex++;
            listExistValue.add(strValues[i].trim());

            strbdrInSQL.append(" or ").append(strFieldCode).append(" like '").append(strValues[i]).append(SINGLE_QUOTE);

            if (iCount == -1 && i == strValues.length - 1)
            {
                listLikeSQL.add(strbdrInSQL.substring(4));
                continue;
            }

            if (iIndex == iCount || i == strValues.length - 1)
            {
                if (strbdrInSQL.toString().trim().length() > 0)
                {
                    listLikeSQL.add(strbdrInSQL.substring(4));
                }

                iIndex = 0;
                strbdrInSQL = new StringBuilder();
            }
        }

        if (strbdrInSQL.length() > 0 && !listLikeSQL.contains(strbdrInSQL.toString()))// 保证最后一个也在in语句的列表中
        {
            listLikeSQL.add(strbdrInSQL.substring(4));
        }

        return listLikeSQL.toArray(new String[0]);
    }

    /***
     * *********************************************************** <br>
     * 说明：
     *
     * @String
     * @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:06:06
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    protected static String joinToString(Object[] objArray)
    {
        if (objArray == null || objArray.length == 0)
        {
            return null;
        }

        List< String > listExist = new ArrayList< String >();

        StringBuilder sb = new StringBuilder();

        for (int i = 0; i < objArray.length; i++)
        {
            // 判断重复
            if (listExist.contains(objArray[i].toString()))
            {
                continue;
            }

            listExist.add(objArray[i].toString());

            sb.append(SINGLE_QUOTE);
            sb.append(objArray[i].toString());
            sb.append("',");
        }

        if (sb.length() > 0)
        {
            sb.deleteCharAt(sb.length() - 1);
        }

        listExist.clear();

        return sb.toString();
    }

    /***
     * *********************************************************** <br>
     * 说明： 用在拼接sql语句中，把一个单引号转译成两个单引号，以防止SQL注入，调用这个方法不用区分前后台代码<br>
     * 需要commons-lang-2.1.jar
     *
     * @String
     * @author licheng
     * @date Created on 2019年8月5日
     * @time 上午12:05:11
     * @version 1.0 <br>
     * ************************************************************          <br>
     */
    public static String transfer(String strValue)
    { return StringUtil.replace(strValue, SINGLE_QUOTE, "''"); }

    public static String getInsertSQL(String tableName, Map< ?,? > fieldMap)
    { return getInsertSQL(tableName, fieldMap, null); }

    public static String getInsertSQLBySeat(String tableName, Map< ?,? > fieldMap)
    { return getInsertSQLBySeat(tableName, fieldMap, null); }

    public static String getInsertSQL(String tableName, Map< ?,? > fieldMap, String pkName)
    { return handleInsertSQL(tableName, fieldMap, pkName, Boolean.FALSE); }

    public static String getInsertSQLBySeat(String tableName, Map< ?,? > fieldMap, String pkName)
    { return handleInsertSQL(tableName, fieldMap, pkName, Boolean.TRUE); }

    /************************************************************** <br>
     *
     *说明: <br>
     *
     * @Description   <br>
     * @Date Created on 2022/2/14  14:28  <br>
     * @param tableName    表名
     * @param fieldMap     插入的列，值
     * @param pkName       主鍵名称
     * @param isSeat  是否占位符 true：？false：value<br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    private static String handleInsertSQL(String tableName, Map< ?,? > fieldMap, String pkName, Boolean isSeat)
    {
        StringBuffer into = new StringBuffer();
        StringBuffer values = new StringBuffer();
        for (Map.Entry< ?,? > entry : fieldMap.entrySet())
        {
            Object key = entry.getKey();
            Object value = fieldMap.get(key);
            if (String.valueOf(key).equals(pkName))
            { value = UUID.randomUUID().toString(); }
            into.append(key).append(COMMA);
            values.append(transSubSQL(isSeat, entry.getValue())).append(COMMA);
        }

        into.setLength(into.length() - 1);
        values.setLength(values.length() - 1);

        return String.format(insertSQL, tableName
                , StringUtil.toString(into)
                , StringUtil.toString(values));

    }

    /***
     * *********************************************************** <br>
     * 说明：
     *
     * @String
     * @author licheng
     * @date Created on 2019-7-19
     * @time 上午11:04:33
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see
     */
    public static String getDeleteSQLByPK(String tableName, String pkName)
    {
        return String.valueOf(new StringBuffer(delete).append(tableName)
                                                      .append(where).append(pkName)
                                                      .append(equals).append(QUESTION_MARK));
    }

    public static String getDeleteSQL(String tableName, String whereStr)
    { return String.valueOf(new StringBuffer(String.format(deleteSQL, tableName, whereStr))); }

    public static String getDeleteSQL(String tableName, Map< ?,? > condMap)
    { return handleDeleteSQL(tableName, condMap, Boolean.FALSE); }

    public static String getDeleteSQLBySeat(String tableName, Map< ?,? > condMap)
    { return handleDeleteSQL(tableName, condMap, Boolean.TRUE); }

    /***
     * *********************************************************** <br>
     * 说明： map 条件 key=value<br>
     *
     * @param tableName
     * @param condMap
     * @param isSeat    是否占位符 true：？false：value<br>
     * @return <br>
     * @String <br>
     * @methods nc.pub.itf.tools.sql.SQLHelper#getDeleteSQL <br>
     * @author licheng <br>
     * @date Created on 2019-9-20 <br>
     * @time 下午4:30:50 <br>
     * @version 1.0 <br>
     * ************************************************************          <br>
     * @see <br>
     */
    private static String handleDeleteSQL(String tableName, Map< ?,? > condMap, Boolean isSeat)
    {
        if ((null == condMap ? 0 : condMap.size()) < 1)
        { return null; }
        StringBuffer sql = new StringBuffer(delete).append(tableName).append(where);
        for (Map.Entry< ?,? > entry : condMap.entrySet())
        { sql.append(entry.getKey()).append(equals).append(transSubSQL(isSeat, entry.getValue())).append(and); }
        sql.setLength(sql.length() - 4);

        return sql.toString();
    }

    /************************************************************** <br>
     *
     *说明: 得到带占位符的修改（uodate）SQl语句<br>
     *
     * @Description   <br>
     * @Date Created on 2022/2/14  12:34  <br>
     * @param tableName
     * @param fieldMap
     * @param contMap <br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getUpdateSQLBySeat(String tableName, Map< ?,? > fieldMap, Map< ?,? > contMap)
    { return handleUpdateSQL(tableName, fieldMap, contMap, Boolean.TRUE); }

    /************************************************************** <br>
     *
     *说明: 得到不带占位符的修改（uodate）SQl语句<br>
     *
     * @Description   <br>
     * @Date Created on 2022/2/14  12:34  <br>
     * @param tableName
     * @param fieldMap
     * @param contMap <br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getUpdateSQL(String tableName, Map< ?,? > fieldMap, Map< ?,? > contMap)
    { return handleUpdateSQL(tableName, fieldMap, contMap, Boolean.FALSE); }

    public static String getUpdateSQL(String tableName, Map< ?,? > fieldMap, String whereStr)
    {
        StringBuffer sql = new StringBuffer();
        for (Map.Entry< ?,? > entry : fieldMap.entrySet())
        {
            sql.append(entry.getKey())
               .append(equals)
               .append(transSubSQL(Boolean.FALSE, entry.getValue()))
               .append(COMMA);
        }
        sql.setLength(sql.length() - 1);

        return String.format(updateSQL, tableName, sql, whereStr);
    }

    public static String getUpdateByIdSQL(String tableName, Map< ?,? > dataMap, String primaryKey)
    { return getUpdateByIdSQL(tableName, dataMap, null, primaryKey); }

    public static String getUpdateByIdSQL(String tableName, Map< ?,? > dataMap, Collection fields, String primaryKey)
    {
        StringBuffer sql = new StringBuffer();
        for (Map.Entry< ?,? > entry : dataMap.entrySet())
        {
            if (Optional.ofNullable(fields).isPresent())
            {
                if (fields.contains(entry.getKey()))
                {
                    sql.append(entry.getKey())
                       .append(equals)
                       .append(transSubSQL(Boolean.FALSE, entry.getValue()))
                       .append(COMMA);
                }
            }
            else
            {
                sql.append(entry.getKey())
                   .append(equals)
                   .append(transSubSQL(Boolean.FALSE, entry.getValue()))
                   .append(COMMA);
            }
        }
        sql.setLength(sql.length() - 1);

        return String.format(updateSQL, tableName, sql
                , new StringBuffer(primaryKey).append(equals)
                                              .append(SINGLE_QUOTE)
                                              .append(dataMap.get(primaryKey))
                                              .append(SINGLE_QUOTE));
    }

    /************************************************************** <br>
     *
     *说明: 获取修改SQL语句<br>
     *
     * @Description   <br>
     * @Date Created on 2022/2/14  11:48  <br>
     * @param tableName  表名
     * @param fieldMap   需要更新发字段
     * @param contMap  语句条件<br>
     * @param isSeat  是否占位符 true：？false：value<br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    private static String handleUpdateSQL(String tableName, Map< ?,? > fieldMap, Map< ?,? > contMap, Boolean isSeat)
    {
        StringBuffer setSql = new StringBuffer();
        if ((null == fieldMap ? 0 : fieldMap.size()) < 1 || (null == contMap ? 0 : contMap.size()) < 1)
        { return null; }
        for (Map.Entry< ?,? > entry : fieldMap.entrySet())
        { setSql.append(entry.getKey()).append(equals).append(transSubSQL(isSeat, entry.getValue())).append(COMMA); }
        setSql.setLength(setSql.length() - 1);

        StringBuffer whereSql = new StringBuffer();
        for (Map.Entry< ?,? > entry : contMap.entrySet())
        { whereSql.append(entry.getKey()).append(equals).append(transSubSQL(isSeat, entry.getValue())).append(and); }
        whereSql.setLength(whereSql.length() - 4);

        return String.format(updateSQL, tableName, StringUtil.toString(setSql), StringUtil.toString(whereSql));
    }

    public static String getSelectSQL(String tableName, String[] fields, LinkedHashMap< String,Object > condMap)
    { return getSelectSQL(tableName, fields, condMap, null); }

    public static String getSelectSQL(String tableName, String[] fields, LinkedHashMap< String,Object > condMap,
            LinkedHashMap< String,Object > orderMap)
    { return handleSelectSQL(tableName, fields, condMap, orderMap, Boolean.FALSE); }

    public static String getSelectSQLBySeat(String tableName, String[] fields, LinkedHashMap< String,Object > condMap)
    { return getSelectSQLBySeat(tableName, fields, condMap, null); }

    public static String getSelectSQLBySeat(String tableName, String[] fields, LinkedHashMap< String,Object > condMap,
            LinkedHashMap< String,Object > orderMap)
    { return handleSelectSQL(tableName, fields, condMap, orderMap, Boolean.TRUE); }


    /************************************************************** <br>
     *
     *说明: <br>
     * @Description   <br>
     * @Date Created on 2022/2/14  10:50  <br>
     * @param tableName  表名
     * @param fields 查询的字段  c
     * @param condMap  条件manp key：字段名称 value：字段值
     * @param orderMap 排序map key：排序字段，value：DESC或ASC<br>
     * @param isSeat  是否占位符 true：？false：value<br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    private static String handleSelectSQL(String tableName, String[] fields, Map< String,Object > condMap,
            LinkedHashMap< String,Object > orderMap, Boolean isSeat)
    {
        StringBuffer sql = getSelectSQL(tableName, fields);
        if ((null == condMap ? 0 : condMap.size()) > 0)
        {
            sql.append(where);
            for (Map.Entry< ?,? > entry : condMap.entrySet())
            {
                sql.append(entry.getKey()).append(equals).append(transSubSQL(isSeat, entry.getValue()));
                sql.append(and);
            }
            sql.setLength(sql.length() - 4);
            sql.append(SPACE);
        }
        if ((null == orderMap ? 0 : orderMap.size()) > 0)
        {
            sql.append(order_by);
            // entrySet遍历(墙裂推荐！！！）
            for (Map.Entry< ?,? > entry : orderMap.entrySet())
            { sql.append(entry.getKey()).append(SPACE).append(entry.getValue()).append(COMMA); }
            sql.setLength(sql.length() - 1);

            // //  2. 在for循环中遍历key或者value，一般适用于只需要map中的key或者value时使用，在性能上比使用entrySet较好
            // //key
            // for (String key : orderMap.keySet())
            // { System.out.println(key); }
            // //value
            // for (String value : orderMap.values())
            // { System.out.println(value); }

            // // 3. 通过Iterator遍历
            // Iterator< Map.Entry< String,String > > entries = orderMap.entrySet().iterator();
            // while (entries.hasNext())
            // {
            //     Map.Entry< String,String > entry = entries.next();
            //     String key = entry.getKey();
            //     String value = entry.getValue();
            //     System.out.println(key + " : " + value);
            // }

            // // 4.通过键找值遍历，这种方式的效率比较低，因为本身从键取值是耗时的操作
            // for (String key : orderMap.keySet())
            // {
            //     String value = orderMap.get(key);
            //     System.out.println(key + " : " + value);
            // }

            // // 5.java8 Lambda, 性能低于entrySet,所以更推荐用entrySet的方式
            // orderMap.forEach((key, value) ->
            //                  {
            //                      System.out.println(key + " : " + value);
            //                  });

        }

        return sql.toString();
    }

    /************************************************************** <br>
     *
     *说明: 获取SELECT SQL语句 <br>
     *
     * @Description   <br>
     * @Date Created on 2022/3/3  18:46  <br>
     * @param tableName  表名
     * @param fields   需要查询的列
     * @param whereStr 必传where条件，可以在其后跟order by 语句<br>
     * @return java.lang.String  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    public static String getSelectSQL(String tableName, String[] fields, String whereStr, String orderStr)
    {
        StringBuffer sql = getSelectSQL(tableName, fields);
        sql.append(where).append(whereStr);
        if (PubEnvUtil.isEmpty(whereStr))
        { sql.append(" 调用此方法 SQL语句条件值不允许为空!!"); }
        if (!PubEnvUtil.isEmpty(orderStr))
        { sql.append(order_by).append(orderStr); }

        return sql.toString();
    }

    private static StringBuffer getSelectSQL(String tableName, String[] fields)
    {
        if (fields == null)
        { return new StringBuffer().append(String.format(selectSQL, ASTERISK, tableName)); }
        else
        {
            StringBuilder fieldSql = new StringBuilder();
            for (int i = 0; i < fields.length; i++)
            { fieldSql.append(fields[i]).append(COMMA); }
            fieldSql.setLength(fieldSql.length() - 1);
            return new StringBuffer(String.format(selectSQL, StringUtil.toString(fieldSql), tableName));
        }

    }

    public static String transSubSQL(Boolean isSeat, Object strValue)
    { return isSeat ? QUESTION_MARK : (SINGLE_QUOTE + strValue + SINGLE_QUOTE); }

    public static String getSingleWhereSQL(Map< String,Object > condMap, String key)
    { return getSingleWhereSQL(key, condMap.get(key)); }

    public static String getSingleWhereSQL(String key, Object Value)
    { return key + IPubCons.EQUALS + IPubCons.SINGLE_QUOTE + Value + IPubCons.SINGLE_QUOTE; }

}
